package br.com.educa.componentes.datatables;

import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;

import org.springframework.web.util.UriUtils;
import org.springframework.web.util.WebUtils;

import br.com.educa.util.ReflectionUtils;

public class DataTablesHql {
	
	
	public static ArrayList<String> doJsonIncludeQuery(Map<String,String[]> parameters){
		ArrayList<String> retorno = new ArrayList<String>();
		HashMap<Integer,Boolean> isList = new HashMap<Integer,Boolean>();
		HashMap<Integer,String> mDataProps = new HashMap<Integer,String>();
		for (Entry<String,String[]> entry : parameters.entrySet()) {
			if(entry.getKey().startsWith("mDataProp_") && !entry.getValue()[0].equals("htmlControl")){
				mDataProps.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("isList_")){
				isList.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}
		}
		String[] aColumns = new String[mDataProps.values().size()];
		int contador=0; 
		for (String mDataProp : mDataProps.values()) {
			aColumns[contador]= mDataProp;
			contador++;
		}
		for ( int i=0 ; i< aColumns.length ; i++ )
		{
			if(isList.get(i)==true  ){
				retorno.add("aaData."+aColumns[i]);
			}
		}
		
		return retorno;
		
	}
	
	@SuppressWarnings({ "unused", "rawtypes", "unchecked" })
	public static TypedQuery doBServerSideHql(Map<String,String[]> parameters, String clazz, String table, String  whereInicial, String whereAdicional){
		
		ReflectionUtils finder = new ReflectionUtils(clazz);
		EntityManager em = finder.getEntityManager();
        
		String[] parts = clazz.split("\\.");
		if(table==null || table.isEmpty() || table.equals("") || table.equals(" ")){
			table = parts[parts.length-1];
        	table += " as o ";
		}
		
		
		HashMap<Integer,Boolean> bRegexs = new HashMap<Integer,Boolean>();
		HashMap<Integer,Boolean> bSearchables = new HashMap<Integer,Boolean>();
		HashMap<Integer,Boolean> bSortables = new HashMap<Integer,Boolean>();
		HashMap<Integer,Integer> iSortCols = new HashMap<Integer,Integer>();
		HashMap<Integer,String> mDataProps = new HashMap<Integer,String>();
		HashMap<Integer,String> insideDataProp = new HashMap<Integer,String>();
		HashMap<Integer,String> sSearchs = new HashMap<Integer,String>();
		HashMap<Integer,String> sSortDirs = new HashMap<Integer,String>();
		HashMap<Integer,Boolean> isList = new HashMap<Integer,Boolean>();
		
		for (Entry<String,String[]> entry : parameters.entrySet()) {
			if(entry.getKey().startsWith("bRegex_")){
				bRegexs.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("bSearchable_")){
				bSearchables.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("bSortable_")){
				bSortables.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("iSortCol_")){
				iSortCols.put(Integer.parseInt(entry.getKey().split("_")[1]), Integer.parseInt(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("mDataProp_") && !entry.getValue()[0].equals("htmlControl")){
				mDataProps.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("insideDataProp_")){
				insideDataProp.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("sSearch_")){
				sSearchs.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("sSortDir_")){
				sSortDirs.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("isList_")){
				isList.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}
		}
		
		String[] aColumns = new String[mDataProps.values().size()];
		int contador=0; 
		for (String mDataProp : mDataProps.values()) {
			aColumns[contador]="o." + mDataProp;
			contador++;
		}
		
		for ( int i=0 ; i< aColumns.length ; i++ )
		{
			if(isList.get(i)==true  ){
				table += " left join fetch "+aColumns[i] +" as "+ mDataProps.get(i);
			}
		}
		
		if(iSortCols.size()<bSearchables.size()){
			for (Entry<Integer,Boolean> entry : bSearchables.entrySet()) {
				if(!iSortCols.containsKey(entry.getKey())){
					iSortCols.put(entry.getKey(), null);
				}
			}
		}
		
		for (Entry<Integer,String> entry : sSearchs.entrySet()) {
			if(entry.getValue().isEmpty()){
				entry.setValue(null);
			}
		}
			
		
		Boolean bRegex = Boolean.parseBoolean(parameters.get("bRegex")[0]);
		Integer iColumns = Integer.parseInt(parameters.get("iColumns")[0]);
		Integer iDisplayLength = Integer.parseInt(parameters.get("iDisplayLength")[0]);
		Integer iDisplayStart = Integer.parseInt(parameters.get("iDisplayStart")[0]);
		Integer iSortingCols = Integer.parseInt(parameters.get("iSortingCols")[0]);
		String sColumns = parameters.get("sColumns")[0];
		Integer sEcho = Integer.parseInt(parameters.get("sEcho")[0]);
		String sSearch="";
		try {
			sSearch = UriUtils.decode(parameters.get("sSearch")[0], WebUtils.DEFAULT_CHARACTER_ENCODING);
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		sSearch = sSearch.replaceAll("'", "").replaceAll("\"", "").replaceAll("\'", "");
		
		
		
		String sOrder= "";
		if(iSortCols.get(0)!=null){
			sOrder = " ORDER BY  ";
			for(int i=0; i<iSortingCols; i++){
				if(bSortables.get(iSortCols.get(i)) == true){
//					if(isList.get(iSortCols.get(i))){
//						sOrder += aColumns[iSortCols.get(i)] + " " + sSortDirs.get(i) + ", ";
//					}else{
						sOrder += aColumns[iSortCols.get(i)] + " " + sSortDirs.get(i) + ", ";
//					}
				}
			}
			
			sOrder=sOrder.substring(0, sOrder.length()-2);
			if(sOrder.equals(" ORDER BY")){
				sOrder="";
			}
		}
		
		
		/* Searching */
		String sWhere = null;
		if(whereInicial==null || whereInicial.isEmpty() || whereInicial.equals(" "))
			sWhere= " where  1=1 ";
		else
			sWhere = whereInicial+" ";
		
		//where adicional
		if(whereAdicional != null && !whereAdicional.isEmpty() && !whereAdicional.equals(" ")){
			sWhere +=  whereAdicional;
		}
		
		if (sSearch != "")
		{
			sWhere+=" and ( ";
			for ( int i=0 ; i< aColumns.length ; i++ )
			{
				if(isList.get(i)==false  ){
					sWhere += "lower(cast("+aColumns[i]+" as string))"+" LIKE '%"+sSearch.toLowerCase()+"%' OR ";
				}else if(isList.get(i)==true  ){
//					sWhere += mDataProps.get(i)+"."+insideDataProp.get(i)+" = '"+sSearch.toLowerCase()+"' OR ";
					sWhere += "lower(cast("+mDataProps.get(i)+"."+insideDataProp.get(i)+" as string))"+" LIKE '%"+sSearch.toLowerCase()+"%' OR ";
				}
			}
			sWhere = sWhere.substring(0, sWhere.length()-3 );
			sWhere += ") ";
		}
		
		/* Individual column filtering  */
		for (int i =0; i<aColumns.length; i++) {
			if(i<bSearchables.size()){
				
				if ( bSearchables.get(i) == true && sSearchs.get(i) != null && isList.get(i)==false  )
				{
					sWhere += " AND ";
					sWhere += "lower(cast("+aColumns[i]+" as string))"+" LIKE '%"+sSearchs.get(i)+"%' ";
				}
				else if( bSearchables.get(i) == true && sSearchs.get(i) != null && isList.get(i)==true  )
				{
					sWhere += " AND ";
					sWhere += aColumns[i]+" = '"+sSearchs.get(i)+"' ";
				}
				
			}
		}
		
		
		StringBuilder hqlBuilder = new StringBuilder();
		hqlBuilder.append("select  o from ")
		.append(table)
		.append(sWhere)
		.append(sOrder);
		
		String hql = hqlBuilder.toString();
		
		TypedQuery q = em.createQuery(hql, finder.getClazz());
		q.setFirstResult(iDisplayStart).setMaxResults(iDisplayLength);
		
		
		return q;
		
	}

	@SuppressWarnings({ "unused", "rawtypes" })
	public static TypedQuery doBServerSideITotalRecordsSql(Map<String,String[]> parameters, String clazz, String table, String  whereInicial, String whereAdicional){
		
		ReflectionUtils finder = new ReflectionUtils(clazz);
		EntityManager em = finder.getEntityManager();
        
		String[] parts = clazz.split("\\.");
		if(table==null || table.isEmpty() || table.equals("") || table.equals(" ")){
			table = parts[parts.length-1];
        	table += " as o ";
		}		
		
		HashMap<Integer,Boolean> bRegexs = new HashMap<Integer,Boolean>();
		HashMap<Integer,Boolean> bSearchables = new HashMap<Integer,Boolean>();
		HashMap<Integer,Boolean> bSortables = new HashMap<Integer,Boolean>();
		HashMap<Integer,Integer> iSortCols = new HashMap<Integer,Integer>();
		HashMap<Integer,String> mDataProps = new HashMap<Integer,String>();
		HashMap<Integer,String> insideDataProp = new HashMap<Integer,String>();
		HashMap<Integer,String> sSearchs = new HashMap<Integer,String>();
		HashMap<Integer,String> sSortDirs = new HashMap<Integer,String>();
		HashMap<Integer,Boolean> isList = new HashMap<Integer,Boolean>();
		
		for (Entry<String,String[]> entry : parameters.entrySet()) {
			if(entry.getKey().startsWith("bRegex_")){
				bRegexs.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("bSearchable_")){
				bSearchables.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("bSortable_")){
				bSortables.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("iSortCol_")){
				iSortCols.put(Integer.parseInt(entry.getKey().split("_")[1]), Integer.parseInt(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("mDataProp_") && !entry.getValue()[0].equals("htmlControl")){
				mDataProps.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("insideDataProp_")){
				insideDataProp.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("sSearch_")){
				sSearchs.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("sSortDir_")){
				sSortDirs.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("isList_")){
				isList.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}
		}
		
		String[] aColumns = new String[mDataProps.values().size()];
		int contador=0; 
		for (String mDataProp : mDataProps.values()) {
			aColumns[contador]="o." + mDataProp;
			contador++;
		}
		
		if(iSortCols.size()<bSearchables.size()){
			for (Entry<Integer,Boolean> entry : bSearchables.entrySet()) {
				if(!iSortCols.containsKey(entry.getKey())){
					iSortCols.put(entry.getKey(), null);
				}
			}
		}
		
		for (Entry<Integer,String> entry : sSearchs.entrySet()) {
			if(entry.getValue().isEmpty()){
				entry.setValue(null);
			}
		}
			
		
		Boolean bRegex = Boolean.parseBoolean(parameters.get("bRegex")[0]);
		Integer iColumns = Integer.parseInt(parameters.get("iColumns")[0]);
		Integer iDisplayLength = Integer.parseInt(parameters.get("iDisplayLength")[0]);
		Integer iDisplayStart = Integer.parseInt(parameters.get("iDisplayStart")[0]);
		Integer iSortingCols = Integer.parseInt(parameters.get("iSortingCols")[0]);
		String sColumns = parameters.get("sColumns")[0];
		Integer sEcho = Integer.parseInt(parameters.get("sEcho")[0]);		
		
		
		
		
		/* Searching */
		String sWhere = null;
		if(whereInicial==null || whereInicial.isEmpty() || whereInicial.equals(" "))
			sWhere= " where  1=1 ";
		else
			sWhere = " "+ whereInicial;
		
		//where adicional
//		if(whereAdicional!=null && !whereAdicional.isEmpty() && !whereAdicional.equals(" ")){
//			sWhere +=  whereAdicional;
//		}
		
		
		
		StringBuilder hqlBuilder = new StringBuilder();
		hqlBuilder.append("select count( o) from ")
		.append(table)
		.append(sWhere);
		
		String hql = hqlBuilder.toString();
		
		TypedQuery q = em.createQuery(hql, Long.class);
		
		return q;
		
	}
	
	

}
